package com.softer.chengxin.score.dao.provider;

import com.softer.chengxin.common.domin.Common;
import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.score.entity.ScoreComposite;
import com.softer.chengxin.score.entity.ScoreDetailVO;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * @author wangxinrui
 * @date 2018/10/12
 */
public class ManagerDaoProvider {


    /**
     * 优良负面信息变更审批
     */
    public String biangeng(ScoreDetailVO s) {
        StringBuffer sql = new StringBuffer(20);
        sql.append("  update score_detail set gmt_modified=now() ");
        if (null != s.getState()) {
            sql.append(" ,state=#{state} ");
        }
        if (s.getScoreTypeId() != null) {
            sql.append(" ,score_type_id=#{scoreTypeId} ");
        }
        if (s.getScoreTypeDetailId() != null) {
            sql.append(" ,score_type_detail_id=#{scoreTypeDetailId} ");
        }
        if (null != s.getScore()) {
            sql.append(" ,score=#{score} ");
        }
        if (StringUtils.isNotBlank(s.getZgBgyj())) {
            sql.append(" ,zg_bgyj=#{zgBgyj} ");
        }
        if (StringUtils.isNotBlank(s.getSsnr())) {
            sql.append(" ,ssnr=#{ssnr} ");
        }
        if (StringUtils.isNotBlank(s.getZgBgyj())) {
            sql.append(" ,zg_bgyj=#{zgBgyj} ");
        }
        if (StringUtils.isNotBlank(s.getSsnr())) {
            sql.append(" ,ssnr=#{ssnr} ");
        }
        sql.append(" where id=#{id}");
        return sql.toString();
    }

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     */
    public String spNo(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String ssnr = (String) map.get("ssnr");
        String score = (String) map.get("score");
        String scoreTypeId = (String) map.get("scoreTypeId");
        sql.append(" update score_detail set state=#{state},shyj=#{shyj},approver=#{approver} ");
        if (StringUtils.isNotBlank(ssnr)) {
            sql.append(" ,ssnr=#{ssnr} ");
        }
        if (StringUtils.isNotBlank(score)) {
            sql.append(" ,score=#{score} ");
        }
        if (StringUtils.isNotBlank(scoreTypeId)) {
            sql.append(" ,score_type_id=#{scoreTypeId} ");
        }
        sql.append("  where id=#{id} ");
        return sql.toString();
    }

    public void commonSql(StringBuffer sql, String area, String qyName, String dj, String year) {
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(dj)) {
            sql.append(" and r.dj=#{dj}");
        }
        if (StringUtils.isNotBlank(year)) {
            sql.append(" and r.year=#{year}");
        } else {
            sql.append(" and r.year=DATE_FORMAT(NOW(), '%Y')");
        }
    }

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表
     */
    public String getScoreDetailList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");

        sql.append("select c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,m.name as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id left join manager_user m on m.login_name=d.approver ");
        sql.append(" where d.info_type=#{infoType} ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0, (CASE c.community WHEN 0 THEN FIND_IN_SET(c.street,#{area}) ELSE FIND_IN_SET( c.community,#{area}) end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,#{area}) ELSE FIND_IN_SET(p.community,#{area}) end))");


        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        } else {
            sql.append(" and d.state in (1,2,3,4,5,6,7,9,10,11,12)");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表 条数
     */
    public String getScoreDetailListCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");

        sql.append("select count(*) ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType}");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0, (CASE c.community WHEN 0 THEN FIND_IN_SET(c.street,#{area}) ELSE FIND_IN_SET( c.community,#{area}) end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,#{area}) ELSE FIND_IN_SET(p.community,#{area}) end))");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and d.state in (").append(state).append(")");
        } else {
            sql.append(" and d.state in (1,2,3,4,5,6,7,9,10,11,12)");
        }
        return sql.toString();
    }

    /**
     * 获取负面信息录入界面的信息列表
     */
    public String getScoreAddList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String adder = (String) map.get("adder");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        Integer infoType = (Integer) map.get("infoType");
        Page page = (Page) map.get("page");

        sql.append("select c.`name` as qyName,");
        sql.append(" d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id,(select @rowno:=1) rn \n");
        sql.append(" where d.adder='").append(adder).append("'");
        sql.append(" and d.state=0");
        sql.append(" and d.info_type=").append(infoType);
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%','").append(proName).append("','%')");
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 获取负面信息录入界面的信息列表 条数
     */
    public String getScoreAddListCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String adder = (String) map.get("adder");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        Integer infoType = (Integer) map.get("infoType");

        sql.append("select count(*)");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id,(select @rowno:=1) rn \n");
        sql.append(" where d.adder='").append(adder).append("'");
        sql.append(" and d.state=0");
        sql.append(" and d.info_type=").append(infoType);
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%','").append(proName).append("','%')");
        }

        return sql.toString();
    }

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     */
    public String getScoreDetailByYear(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        Integer infoType = (Integer) map.get("infoType");
        String effectiveBeginTime = (String) map.get("effectiveBeginTime");
        String effectiveEndTime = (String) map.get("effectiveEndTime");
        Integer corpId = (Integer) map.get("corpId");
        String year = (String) map.get("year");

        sql.append("select c.`name` as qyName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d,company_base_info c ");
        sql.append(" where c.id=d.corp_id and c.state in (1,2,3) and d.state=7");

        commonCode(sql, area, qyName, infoType, effectiveBeginTime, effectiveEndTime, corpId, year);

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());

        return sql.toString();
    }

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息） 条数
     */
    public String getScoreDetailByYearCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        Integer infoType = (Integer) map.get("infoType");
        String effectiveBeginTime = (String) map.get("effectiveBeginTime");
        String effectiveEndTime = (String) map.get("effectiveEndTime");
        Integer corpId = (Integer) map.get("corpId");
        String year = (String) map.get("year");

        sql.append("select count(*)");
        sql.append(" from score_detail d,company_base_info c");
        sql.append(" where c.id=d.corp_id and c.state in (1,2,3) and d.state=7");

        commonCode(sql, area, qyName, infoType, effectiveBeginTime, effectiveEndTime, corpId, year);

        return sql.toString();
    }

    public void commonCode(StringBuffer sql, String area, String qyName, Integer infoType, String effectiveBeginTime, String effectiveEndTime, Integer corpId, String year) {
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(area).append("','%')");
        }
        if (infoType != null) {
            sql.append(" and d.info_type=").append(infoType);
        }
        if (StringUtils.isNotBlank(effectiveBeginTime)) {
            sql.append(" and d.effective_begin_time>=").append(effectiveBeginTime);
        }
        if (StringUtils.isNotBlank(effectiveEndTime)) {
            sql.append(" and d.effective_end_time<=").append(effectiveEndTime);
        }
        if (corpId != null) {
            sql.append(" and d.corp_id=").append(corpId);
        }
        if (StringUtils.isNotBlank(year)) {
            sql.append(" and year(d.effective_begin_time)=").append(year);
        }
    }

    /**
     * 获取变更页面列表
     */
    public String getBianGengList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");
        String area = (String) map.get("area");
        sql.append("select c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType} ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0, (CASE c.community WHEN 0 THEN FIND_IN_SET(c.street,#{area}) ELSE FIND_IN_SET( c.community,#{area}) end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,#{area}) ELSE FIND_IN_SET(p.community,#{area}) end))");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and FIND_IN_SET(d.state ,#{state})");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 获取变更页面列表 条数
     */
    public String getBianGengListCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        String state = (String) map.get("state");
        String area = (String) map.get("area");
        sql.append("select count(*) ");
        sql.append(" from score_detail d left join company_base_info c on c.id=d.corp_id left join company_project p on d.project_id=p.id");
        sql.append(" where d.info_type=#{infoType} ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0, (CASE c.community WHEN 0 THEN FIND_IN_SET(c.street,#{area}) ELSE FIND_IN_SET( c.community,#{area}) end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,#{area}) ELSE FIND_IN_SET(p.community,#{area}) end))");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and FIND_IN_SET(d.state ,#{state})");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        return sql.toString();
    }

    /**
     * 查询企业信用年度评价列表
     *
     * @param map 查询实体
     * @return 结果
     */
    public String getYearScoreDetail(Map<String, ScoreComposite> map) {
        StringBuilder sql = new StringBuilder(6);
        ScoreComposite scoreComposite = map.get("sc");
        String area = scoreComposite.getArea();
        sql.append("SELECT c.`name` AS qyName, c.business_license_no AS businessLicenseNo, r.id AS id, r.basic_score AS basicScore,");
        sql.append("r.excellent_score AS excellentScore, r.negative_score AS negativeScore, r.all_score AS allScore, r.company_id AS companyId,");
        sql.append("r.gmt_create AS gmtCreate, (CASE c.level_state WHEN 1 THEN r.`level` ELSE '已撤销' END) AS level, r.`year` AS year, r.gmt_modified AS gmtModified");
        sql.append(" FROM score_composite r LEFT JOIN company_base_info c ON r.company_id = c.id");
        sql.append(" WHERE c.state IN (2, 9)");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(scoreComposite.getQyName())) {
            sql.append(" AND INSTR(c.name,#{sc.qyName})>0 ");
        }
        if (StringUtils.isNotBlank(scoreComposite.getLevel())) {
            if (Common.CANCEL.equals(scoreComposite.getLevel())) {
                sql.append(" and c.level_state = 0");
            } else {
                sql.append(" and r.level=#{sc.level}");
            }
        }
        if (StringUtils.isNotBlank(scoreComposite.getYear())) {
            sql.append(" and r.year=#{sc.year}");
        }
        sql.append(" order by r.all_score desc");
        return sql.toString();
    }
    /**
     * 查询省平台推送的
     * 企业信用年度评价列表
     *
     * @param map 查询实体
     * @return 结果
     */
    public String getYearScoreDetailFromProvince(Map<String, ScoreComposite> map) {
        StringBuilder sql = new StringBuilder(6);
        sql.append("SELECT c.`name` AS qyName, c.business_license_no AS businessLicenseNo, r.id AS id, r.basic_score AS basicScore,");
        sql.append("r.excellent_score AS excellentScore, r.negative_score AS negativeScore, r.all_score AS allScore, r.company_id AS companyId,");
        sql.append("r.gmt_create AS gmtCreate, (CASE c.level_state WHEN 1 THEN r.`level` ELSE '已撤销' END) AS level, r.`year` AS year, r.gmt_modified AS gmtModified");
        sql.append(" FROM score_composite_province r LEFT JOIN company_base_info c ON r.company_id = c.guid");
        commonSql(sql, map);
        return sql.toString();
    }
    private void commonSql(StringBuilder sql, Map<String, ScoreComposite> map) {
        sql.append(" WHERE c.state IN (2, 9)");
        ScoreComposite scoreComposite = map.get("sc");
        String area = scoreComposite.getArea();
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(scoreComposite.getQyName())) {
            sql.append(" AND INSTR(c.name,#{sc.qyName})>0 ");
        }
        if (StringUtils.isNotBlank(scoreComposite.getLevel())) {
            if (Common.CANCEL.equals(scoreComposite.getLevel())) {
                sql.append(" and c.level_state=0");
            } else {
                sql.append(" and r.level=#{sc.level}");
            }
        }
        if (StringUtils.isNotBlank(scoreComposite.getYear())) {
            sql.append(" and r.year=#{sc.year}");
        }
        sql.append(" order by r.all_score desc");
    }
}
